<?php
class Taxonomies extends Dao
{
	private static $_instance = null;
	protected $_db = null, $_cache = null;
	public $_arrFieldAtrributes = array(
		'input_types' => array(
			'text' => 'Text',
			'password' => 'Password',
			'radio' => 'Radio',
			'checkbox' => 'Checkbox',
			'textarea' => 'Text-area',
			'file' => 'File'
		),
		'data_types' => array(
			'text' => 'Text',
			'number' => 'Number'			
		),
	);
	
	protected function __construct()
	{
		parent::__construct();
	}
	
	public static function getInstance() 
    {
        if (null === self::$_instance) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }
	
	public function getListStatus(){
		return $this->_arrStatus;
	}
	
	public function getList()
	{
		try{
			return Yii::app()->db->createCommand()->select('taxonomy_id, taxonomy_code, taxonomy_name, taxonomy_description')
    											  ->from('tbl_cms_taxonomies')
    											  ->queryAll();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}	
	}
	
	public function get($id)
	{
		try{
			return Yii::app()->db->createCommand()->select('taxonomy_id, taxonomy_code, taxonomy_name, taxonomy_description')
    											  ->from('tbl_cms_taxonomies')
												  ->where('taxonomy_id = :id', array(':id'=>$id))
    											  ->queryRow();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}	
	}
	
	public function insert(array $args = array())
	{
		try{
			$defaults = array(
				'taxonomy_id' => null,
				'taxonomy_code' => null,
				'taxonomy_name' => null,
				'taxonomy_description' => null				
			);
			$args = $this->args_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			$cmd->insert('tbl_cms_taxonomies',$args);
			return $intInsertId = Yii::app()->db->getLastInsertId();				
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function update($id, array $args = array())
	{
		try{
			$defaults = array(
				'taxonomy_code' => null,
				'taxonomy_name' => null,
				'taxonomy_description' => null				
			);
			$args = $this->args_merge($defaults, $args);
			$cmd = $this->_db->createCommand();
			$intAffectedRows = $cmd->update('tbl_cms_taxonomies',$args, 'taxonomy_id=:id', array(':id'=>$id));

			return 1;			
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function delete($id)
	{
		try{
			//delete posts from categories
			$strSql = "DELETE pc FROM tbl_cms_post_category AS pc INNER JOIN tbl_cms_posts AS p ON pc.post_id = p.post_id WHERE p.taxonomy_id = :id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$id,PDO::PARAM_INT);
			$cmd->execute();
			//delete posts from tags
			$strSql = "DELETE pt FROM tbl_cms_post_tag AS pt INNER JOIN tbl_cms_posts AS p ON pt.post_id = p.post_id WHERE p.taxonomy_id = :id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$id,PDO::PARAM_INT);
			$cmd->execute();
			//delete posts from fields
			$strSql = "DELETE pf FROM tbl_cms_post_field AS pf INNER JOIN tbl_cms_posts AS p ON pf.post_id = p.post_id WHERE p.taxonomy_id = :id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$id,PDO::PARAM_INT);
			$cmd->execute();
			//delete posts from comments
			$strSql = "DELETE c FROM tbl_cms_comments AS c INNER JOIN tbl_cms_posts AS p ON c.post_id = p.post_id WHERE p.taxonomy_id = :id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$id,PDO::PARAM_INT);
			$cmd->execute();
			//delete from sentiment
			$strSql = "DELETE ps FROM tbl_cms_post_sentiment AS ps INNER JOIN tbl_cms_posts AS p ON ps.post_id = p.post_id WHERE p.taxonomy_id = :id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$id,PDO::PARAM_INT);
			$cmd->execute();
			//delete post children
			$strSql = "DELETE FROM tbl_cms_posts WHERE post_parent IN ( SELECT * FROM (SELECT post_id FROM tbl_cms_posts WHERE taxonomy_id = :id) AS temp)";
			//$strSql = "DELETE children FROM tbl_cms_posts AS children INNER JOIN tbl_cms_posts AS parent WHERE children.post_parent = parent.post_id AND parent.taxonomy_id = :id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$id,PDO::PARAM_INT);
			$cmd->execute();
			//delete post
			$cmd->delete('tbl_cms_posts', 'taxonomy_id=:id', array(':id'=>$id));	
			//delete taxonomy
			$intAffectedRows = $cmd->delete('tbl_cms_taxonomies', 'taxonomy_id=:id', array(':id'=>$id));
			return $intAffectedRows;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getListFields($taxonomyId, $assoc = false)
	{
		try{
			$strSql = " SELECT field_id, taxonomy_id, field_code, field_name, field_description, field_position, field_attributes
			 			FROM tbl_cms_fields WHERE taxonomy_id = :id ";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$taxonomyId,PDO::PARAM_INT);
			$arrFields = $cmd->queryAll();
						
			foreach($arrFields as $key=>$R){
				if(!empty($R['field_attributes'])){
					$arrFields[$key]['field_attributes'] = json_decode($arrFields[$key]['field_attributes'], true);
				}				
			}
			
			if($assoc == true){
				$arrAssocFields = array();
				foreach($arrFields as $key=>$R){
					$arrAssocFields[$R['field_code']] = $R;
				}
				$arrFields = $arrAssocFields;
			}
			return $arrFields;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getField($fieldId)
	{
		try{
			$strSql = " SELECT field_id, taxonomy_id, field_code, field_name, field_description, field_position, field_attributes
			 			FROM tbl_cms_fields WHERE field_id = :id ";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$fieldId,PDO::PARAM_INT);
			$arrFields = $cmd->queryRow();
			
			if(!empty($arrFields['field_attributes'])){
				$arrFields['field_attributes'] = json_decode($arrFields['field_attributes'], true);
			}
			return $arrFields;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function insertField(array $args = array())
	{
		try{
			$defaults = array(
				'taxonomy_id' => null,
				'field_code' => null,
				'field_name' => null,
				'field_description' => null,
				'field_attributes' => null,
				'field_position' => null				
			);
			$args = $this->args_merge($defaults, $args);
			if(!empty($args['field_attributes'])){
				$args['field_attributes'] = json_encode($args['field_attributes']);
			}
			
			$cmd = $this->_db->createCommand();
			$cmd->insert('tbl_cms_fields',$args);
			$intInsertId = Yii::app()->db->getLastInsertId();
					
			return $intInsertId;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function updateField($fieldId, array $args = array())
	{
		try{
			$defaults = array(
				'taxonomy_id' => null,
				'field_code' => null,
				'field_name' => null,
				'field_description' => null,
				'field_attributes' => null,
				'field_position' => null				
			);
			$args = $this->args_merge($defaults, $args);
			if(!empty($args['field_attributes'])){
				$args['field_attributes'] = json_encode($args['field_attributes']);
			}

			$cmd = $this->_db->createCommand();
			$intAffectedRows = $cmd->update('tbl_cms_fields',$args, 'field_id=:id', array(':id'=>$fieldId));
					
			return 1;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function deleteField($fieldId)
	{
		try{
			//delete field from posts
			$strSql = "DELETE FROM tbl_cms_post_field WHERE field_id = :id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$fieldId,PDO::PARAM_INT);
			$cmd->execute();				
			//delete field
			$intAffectedRows = $cmd->delete('tbl_cms_fields', 'field_id=:id', array(':id'=>$fieldId));
			return $intAffectedRows;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function generateField($field, $defaulValue = null, $includeLabel = false)
	{
		//parse field's attributes
		$fieldTitle = $field['field_name'];
		$fieldDesc =  $field['field_description'];
		$fieldType = 'type="'.$field['field_attributes']['input_type'].'"';
		$fieldName = 'name="fields['.$field['field_id'].']"';
		$fieldId = 'id="field_'.$field['field_id'].'"';
		$fieldValue = 'value="'.($defaulValue !== null?$defaulValue:$field['field_attributes']['value']).'"';
		$fieldStyle = 'style="'.$field['field_attributes']['style'].'"';
		$fieldClass = isset($field['field_attributes']['class'])?'class="'.$field['field_attributes']['class'].'"':'';
		$fieldMaxlength = !empty($field['field_attributes']['maxlength'])?'maxlength="'.$field['field_attributes']['maxlength'].'"':null;
		$fieldRequired = (isset($field['field_attributes']['required']) && $field['field_attributes']['required'] == '1')?'requried':'';
		
		$html = ''; 
		
		//generate label
		if($includeLabel){
			$span = $fieldRequired == 'required'?' <span class="red">*</span>':'';
			$html .= "<label for=\"field_{$field['field_id']}\">{$fieldTitle} {$span}</label>";
		}
		
		//generate field
		if(!empty($field['field_attributes']['input_type'])){
			switch($field['field_attributes']['input_type']){
				case 'text':
				case 'password':					
					$html .= "<input {$fieldType} {$fieldName} {$fieldId} {$fieldClass} {$fieldStyle} {$fieldRequired} {$fieldMaxlength} {$fieldValue}/>";
					break;
				case 'textarea':
					$fieldValue = ($defaulValue !== null)?$defaulValue:$field['field_attributes']['value'];
					$html .= "<textarea {$fieldName} {$fieldId} {$fieldClass} {$fieldStyle} {$fieldRequired}>{$fieldValue}</textarea>";
					break;
				case 'select':
					if(!empty($field['field_attributes']['options']) && is_array($field['field_attributes']['options']['value'])){
						$options = '';
						foreach($field['field_attributes']['options']['value'] as $key=>$value){		
							$optionSelected = (($defaulValue !== null && $defaulValue == $value) || ($defaulValue == null && $field['field_attributes']['value'] == $value)) ? 'selected=""' :  '' ;
							$optionValue = 'value="'.$value.'"';
							$optionTitle = $field['field_attributes']['options']['title'][$key];
							$options .= "<option {$optionValue} {$optionSelected}>{$optionTitle}</option>";
						}
						$html .= "<select {$fieldName} {$fieldId} {$fieldClass} {$fieldStyle} {$fieldRequired}>{$options}</select>";
					}
					break;
				case 'checkbox':
				case 'radio':
					if(!empty($field['field_attributes']['options']) && is_array($field['field_attributes']['options']['value'])){
						foreach($field['field_attributes']['options']['value'] as $key=>$value){		
							$optionValue = 'value="'.$value.'"';
							$optionChecked = (($defaulValue !== null && $defaulValue == $value) || ($defaulValue == null && $field['field_attributes']['value'] == $value)) ? 'checked=""' :  '' ;
							$optionTitle = $field['field_attributes']['options']['title'][$key];
							$html .= "<label {$fieldClass} {$fieldStyle}><input {$fieldType} {$fieldName} {$fieldId} {$fieldRequired} {$fieldMaxlength} {$optionChecked} {$optionValue} /> {$optionTitle}</label>";
						}
					}
					break;
			}
		}
		
        return $html;	
	}
}